import mysql.connector
import logging


class ConnectorSQL:
    def __init__(self, host="localhost", user="root", passwd="root", database=None, path=None):
        """host
        user
        passwd
        DB (default=None)"""
        if path:
            host, user, passwd, database = open(path, 'r').read().split('\n')

        self.connection = mysql.connector.connect(host=host,
                                                  user=user,
                                                  password=passwd,
                                                  database=database)
        self.cursor = self.connection.cursor()
        self.logger = logging.getLogger("SQLcon")

    def show(self, name):
        """Printing database's content"""
        self.cursor.execute("SELECT * FROM " + name)
        results = self.cursor.fetchall()
        for x in results:
            print(x)

    def filtered_output(self):
        """Printing filtered database's content"""
        self.cursor.execute("SELECT * FROM Parsing ORDER BY post_date ")
        results = self.cursor.fetchall()
        for x in results:
            print(x)

    def get_connection(self, host="localhost", user="root", passwd="", database=None):
        """Connection object getter"""
        self.connection = mysql.connector.connect(host=host,
                                                  user=user,
                                                  password=passwd,
                                                  database=database)
        self.cursor = self.connection.cursor()

    def put_posts(self, posts, table):
        """Function for uploading data about posts to MySQL DataBase.
        :param posts: posts list for load to database
        :param table: connection object
        """
        for post in posts:
            self.insert_post(post[0], post[1], post[2], table)

    def insert_post(self, url, title, post_date, table):
        """Post inserting function (link, title, post date, table name)"""
        try:
            cursor = self.connection.cursor()
            mysql_insert_query = "INSERT INTO " + table + """ (url, title, post_date) 
                                        VALUES (%s, %s, %s) """

            record = (url, title, post_date)
            cursor.execute(mysql_insert_query, record)
            self.connection.commit()
            self.logger.info("Record inserted into {} table: {}".format(table, url))

        except mysql.connector.Error as error:
            print("Failed to insert into MySQL table: {}.".format(error))
        # cursor = connection.cursor()
        # if log:
        #     cursor.execute("SELECT * FROM Parsing")
        #     results = cursor.fetchall()
        #     for x in results:
        #         print(x)

    def create(self, name, typename="table"):
        """Create table or database with fixed categories"""
        try:
            if typename == "table":
                self.cursor.execute("CREATE table " + name + """ (
                ID int auto_increment primary key, 
                url varchar(255), 
                title varchar(511), 
                post_date date
                );""")
            elif typename == "database":
                self.connection.cursor().execute("CREATE database " + name + ';')
        except mysql.connector.errors.DatabaseError as error:
            self.logger.error(error)
            return -1
        self.logger.info("{} '{}' created".format(typename.upper(), name))

    def destroy(self, name, typename="table"):
        """Function deleting table or database"""
        try:
            self.cursor.execute("DROP {} {};".format(typename, name))
        except mysql.connector.errors.DatabaseError as error:
            self.logger.error(error)
            return
        self.logger.info("{} '{}' destroyed".format(typename.upper(), name))

    def clear(self, name, typename="table"):
        """Truncate function"""
        try:
            self.cursor.execute("TRUNCATE {} {};".format(typename, name))
        except mysql.connector.errors.DatabaseError as error:
            self.logger.error(error)
            return
        self.logger.info("{} '{}' cleared".format(typename.upper(), name))

    def rename_table(self, oldname, newname):
        """Function renaming table"""
        self.cursor.execute("RENAME TABLE {} TO {};".format())

    def debug(self, post, table):
        """Debug function"""
        self.logger.info("Record inserted into {} table: {}".format(table, post[0]))
